/*
   PROCEDURES LIST :
   	FIND (p_Nom TEXT) RETURNS Result_t
   	EXIST_CONCEPT (p_Nom TEXT) RETURNS BOOLEAN
   	
   INTARNAL PROCEDURES :
   	FUNCTION FIND_IN_TERMNVDTS (p_Nom TEXT) RETURNS Result_t
   	FIND_IN_TERMVDTS (p_Nom TEXT) RETURNS Result_t
   	FIND_IN_CONCEPTS (p_Nom TEXT) RETURNS Result_t
   	FIND_IN_DOMAINS (p_Nom TEXT) RETURNS Result_t
   	EXIST_ASSOCIATION (p_Nom TEXT, p_Concept1 TEXT, p_Concept2 TEXT, p_Reflex BOOLEAN) RETURNS OID
   
*/


/*
*	Function : FIND
*	Description : Cherche un element dans le thesaurus
*/
CREATE OR REPLACE FUNCTION FIND (p_Nom TEXT) RETURNS Result_t AS $$
DECLARE 
	res Result_t;
BEGIN
	SELECT * FROM FIND_IN_DOMAINS(p_Nom) INTO res;
	IF (res.Nom = 'NULL') THEN
	
		SELECT * FROM FIND_IN_CONCEPTS(p_Nom) INTO res;
		IF (res.Nom = 'NULL') THEN

			SELECT * FROM FIND_IN_TERMVDTS(p_Nom) INTO res;
			IF (res.Nom = 'NULL') THEN

				SELECT * FROM FIND_IN_TERMNVDTS(p_Nom) INTO res;
			END IF;
		END IF;
	END IF;

	RETURN res;
END;
$$ LANGUAGE plpgsql;


/*
*	Function : FIND_IN_TERMNVDTS
*	Description : Chercher dans Terms Non Vedettes 
*/
CREATE OR REPLACE FUNCTION FIND_IN_TERMNVDTS (p_Nom TEXT) RETURNS Result_t AS $$
DECLARE 
	termNonVedette_oid OID;
	termVedette_oid OID;
	domain_oid OID;
	concept_tmp Concept_t;
	domain_tmp Domain_t;
	res Result_t;
BEGIN
/* 0- verification des entrées */
	termNonVedette_oid = (SELECT OID FROM TermNonVdts WHERE Nom = p_Nom);
	IF (termNonVedette_oid IS NULL) THEN
		res.Nom = 'NULL';
		RETURN res;
	END IF;  
	
	res.Nom = p_Nom;
	res.Type = 'TermNonVdts';

/** Récupération du term vedette **/
	termVedette_oid = (SELECT EM FROM TermNonVdts WHERE OID = termNonVedette_oid);
	SELECT * FROM TermVdts WHERE OID = termVedette_oid INTO res.TermVdts;
	
/** Récupération du concept **/
	SELECT * FROM Concepts WHERE TermVedette = termVedette_oid INTO concept_tmp;
	res.Concepts = res.Concepts || ARRAY[concept_tmp];
	
/** Récupération du domain **/
	domain_oid = (SELECT Domain FROM Concepts WHERE TermVedette = termVedette_oid);
	SELECT * FROM Domains WHERE OID = domain_oid INTO domain_tmp;
	res.Domains = res.Domains || domain_tmp;
	
/** Récupération du Thesaurus **/
	SELECT * FROM Thesaurus WHERE OID IN (SELECT Theso FROM Domains WHERE OID = domain_oid) INTO res.Thesaurus;
		
	RETURN res;
END;
$$ LANGUAGE plpgsql;



/*
*	Function : FIND_IN_TERMVDTS
*	Description : Chercher dans Terms Vedettes
*/
CREATE OR REPLACE FUNCTION FIND_IN_TERMVDTS (p_Nom TEXT) RETURNS Result_t AS $$
DECLARE 
	t OID;
	termVedette_oid OID;
	termNonVedette_oids OID[];
	domain_oid OID;
	termNonVedette_tmp TermNonVdt_t;
	concept_tmp Concept_t;
	domain_tmp Domain_t;
	res Result_t;
BEGIN
/* 0- verification des entrées */
	termVedette_oid = (SELECT OID FROM TermVdts WHERE Nom = p_Nom);
	IF (termVedette_oid IS NULL) THEN
		res.Nom = 'NULL';
		RETURN res;
	END IF;  
	
	res.Nom = p_Nom;
	res.Type = 'TermVdts';

/** Récupération des term Non vedette **/
	termNonVedette_oids = termNonVedette_oids || ARRAY[(SELECT EP FROM TermVdts WHERE OID = termVedette_oid)];
	FOREACH t IN  ARRAY termNonVedette_oids LOOP
		SELECT * FROM TermNonVdts WHERE OID = t INTO termNonVedette_tmp;
		res.TermNonVdts = res.TermNonVdts || ARRAY[termNonVedette_tmp];
	END LOOP;
	
/** Récupération du concept **/
	SELECT * FROM Concepts WHERE TermVedette = termVedette_oid INTO concept_tmp;
	res.Concepts = res.Concepts || ARRAY[concept_tmp];
	
/** Récupération du domain **/
	domain_oid = (SELECT Domain FROM Concepts WHERE TermVedette = termVedette_oid);
	SELECT * FROM Domains WHERE OID = domain_oid INTO domain_tmp;
	res.Domains = res.Domains || domain_tmp;
	
/** Récupération du Thesaurus **/
	SELECT * FROM Thesaurus WHERE OID IN (SELECT Theso FROM Domains WHERE OID = domain_oid) INTO res.Thesaurus;
	
	RETURN res;
END;
$$ LANGUAGE plpgsql;


/*
*	Function : FIND_IN_CONCEPTS
*	Description : Chercher dans Concepts
*/
CREATE OR REPLACE FUNCTION FIND_IN_CONCEPTS (p_Nom TEXT) RETURNS Result_t AS $$
DECLARE 
	t OID;
	concept_oid OID;
	termVedette_oid OID;
	termNonVedette_oids OID[];
	associations_oids OID[];
	association_tmp Association_t;
	domain_oid OID;
	ts_oids OID[];
	tg_oids OID[];
	ts_tmp Concept_t;
	tg_tmp Concept_t;
	termNonVedette_tmp TermNonVdt_t;
	concept_tmp Concept_t;
	domain_tmp Domain_t;
	res Result_t;
BEGIN
/* 0- verification des entrées */
	concept_oid = (SELECT OID FROM Concepts WHERE Nom = p_Nom);
	IF (concept_oid IS NULL) THEN
		res.Nom = 'NULL';
		RETURN res;
	END IF;  
	
	res.Nom = p_Nom;
	res.Type = 'Concept';

/** Récupération du term vedette **/
	termVedette_oid = (SELECT TermVedette FROM Concepts WHERE OID = concept_oid);
	SELECT * FROM TermVdts WHERE OID = termVedette_oid INTO res.TermVdts;
			
/** Récupération des term Non vedette **/
	termNonVedette_oids = termNonVedette_oids || ARRAY[(SELECT EP FROM TermVdts WHERE OID = termVedette_oid)];
	FOREACH t IN  ARRAY termNonVedette_oids LOOP
		SELECT * FROM TermNonVdts WHERE OID = t INTO termNonVedette_tmp;
		res.TermNonVdts = res.TermNonVdts || ARRAY[termNonVedette_tmp];
	END LOOP;
	
/** Récupération des concepts spécifiques **/
	ts_oids = ts_oids || ARRAY[(SELECT TS FROM Concepts WHERE OID = concept_oid)];
	FOREACH t IN  ARRAY ts_oids LOOP
		SELECT * FROM Concepts WHERE OID = t INTO ts_tmp;
		res.TS = res.TS || ARRAY[ts_tmp];
	END LOOP;
	
/** Récupération des concepts géneriques **/
	tg_oids = tg_oids || ARRAY[(SELECT TG FROM Concepts WHERE OID = concept_oid)];
	FOREACH t IN  ARRAY tg_oids LOOP
		SELECT * FROM Concepts WHERE OID = t INTO tg_tmp;
		res.TG = res.TG || ARRAY[tg_tmp];
	END LOOP;
	
/** Récupération des Associations **/
	associations_oids = associations_oids || ARRAY[(SELECT OID FROM Associations WHERE Concept1 = concept_oid)];
	associations_oids = associations_oids || ARRAY[(SELECT OID FROM Associations WHERE Concept2 = concept_oid)];
	FOREACH t IN  ARRAY associations_oids LOOP
		SELECT * FROM Associations WHERE OID = t INTO association_tmp;
		res.Associations = res.Associations || ARRAY[association_tmp];
	END LOOP;
		
/** Récupération du domain **/
	domain_oid = (SELECT Domain FROM Concepts WHERE TermVedette = termVedette_oid);
	SELECT * FROM Domains WHERE OID = domain_oid INTO domain_tmp;
	res.Domains = res.Domains || domain_tmp;
	
/** Récupération du Thesaurus **/
	SELECT * FROM Thesaurus WHERE OID IN (SELECT Theso FROM Domains WHERE OID = domain_oid) INTO res.Thesaurus;
	
	RETURN res;
END;
$$ LANGUAGE plpgsql;


/*
*	Function : FIND_IN_DOMAINS
*	Description : Chercher dans Domain
*/
CREATE OR REPLACE FUNCTION FIND_IN_DOMAINS (p_Nom TEXT) RETURNS Result_t AS $$
DECLARE 
	t OID;
	concepts_oids OID[];
	domain_oid OID;
	concept_tmp Concept_t;
	domain_tmp Domain_t;
	res Result_t;
BEGIN
/* 0- verification des entrées */
	domain_oid = (SELECT OID FROM Domains WHERE Nom = p_Nom);
	IF (domain_oid IS NULL) THEN
		res.Nom = 'NULL';
		RETURN res;
	END IF;  
	
	res.Nom = p_Nom;
	res.Type = 'Domain';
	
/** Récupération des concepts **/
	concepts_oids = concepts_oids || ARRAY[(SELECT Concepts FROM Domains WHERE OID = domain_oid)];
	FOREACH t IN  ARRAY concepts_oids LOOP
		SELECT * FROM Concepts WHERE OID = t INTO concept_tmp;
		res.Concepts = res.Concepts || ARRAY[concept_tmp];
	END LOOP;

/** Récupération du domain **/
	SELECT * FROM Domains WHERE OID = domain_oid INTO domain_tmp;
	res.Domains = res.Domains || domain_tmp;
	
/** Récupération du Thesaurus **/
	SELECT * FROM Thesaurus WHERE OID IN (SELECT Theso FROM Domains WHERE OID = domain_oid) INTO res.Thesaurus;
	
	RETURN res;
END;
$$ LANGUAGE plpgsql;


/*
*	Function : EXIST_CONCEPT
*	Description : Vérifie si un concept existe
*/
CREATE OR REPLACE FUNCTION EXIST_CONCEPT (p_Nom TEXT) RETURNS BOOLEAN AS $$
DECLARE 
	concept_oid OID;
BEGIN
/* 0- verification des entrées */
	concept_oid = (SELECT OID FROM Concepts WHERE Nom = p_Nom);
	IF (concept_oid IS NULL) THEN
		RETURN FALSE;
	ELSE
		RETURN TRUE;
	END IF;  
END;
$$ LANGUAGE plpgsql;

/*
*	Function : EXIST_ASSOCIATION
*	Description : Vérifie si une association existe, retourne l'oid de l'association si elle existe, 0 sinon 
*/
CREATE OR REPLACE FUNCTION EXIST_ASSOCIATION (p_Nom TEXT, p_Concept1 TEXT, p_Concept2 TEXT, p_Reflex BOOLEAN) RETURNS OID AS $$
DECLARE 
	association_oid OID;
BEGIN
/* 0- verification des entrées */
	association_oid = (SELECT OID FROM Associations WHERE Nom = p_Nom AND 
		concept1 = (SELECT OID FROM Concepts WHERE Nom = p_Concept1) AND 
		concept2 = (SELECT OID FROM Concepts WHERE Nom = p_Concept2) AND
		reflex = p_Reflex);
		
	IF (association_oid IS NULL) THEN
		RETURN 0;
	ELSE
		RETURN association_oid ;
	END IF;  
END;
$$ LANGUAGE plpgsql;
